package org.lzy.pt;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.util.StringUtils;
import org.lzy.pt.bean.TaskEx;
import org.lzy.pt.bean.TaskType;
import org.lzy.pt.util.Constants;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class TaskManager {

	private Logger Log = LoggerFactory.getLogger(TaskManager.class);

	private static TaskManager taskManager = new TaskManager();
	
	private final static String LOAD_TASK_LIST = " SELECT o.username username,o.T_DateTime T_DateTime,o.T_Name T_Name,o.T_ExecDateTime T_ExecDateTime," +
												 " o.T_Para T_Para,o.T_Cnt T_Cnt,o.T_Interval T_Interval,o.T_MainVer T_MainVer,o.T_TaskVer T_TaskVer," +
												 " o.T_FuncName T_FuncName,o.T_FuncVer T_FuncVer,o.T_CompleteFlag T_CompleteFlag," +
												 " t.T_CnName T_CnName,p.T_CnName plugin_CnName "+
												 " FROM oftTask o " +
												 " LEFT JOIN oftTaskType t ON o.T_Name=t.T_Name " +
												 " LEFT JOIN oftProgType p ON o.T_FuncName=p.T_Name " +
												 " ORDER BY T_DateTime desc";

	private static final String TASK_COUNT = "SELECT count(*) FROM oftTask";
	
//	private final static String LOAD_TASK_BY_ID = "SELECT * FROM oftTask ";

	private static final String ADD_TASK = "INSERT INTO oftTask (username, T_DateTime, "
			+ "T_Name,T_ExecDateTime,T_Para,T_Cnt,T_Interval,T_MainVer,T_TaskVer,T_FuncName,T_FuncVer,"
			+ "T_CompleteFlag) VALUES (?, ?, ?,?,?,?,?,?,?,?,?,?)";
	
	private static final String UPDATE_TASK_FLAG = "UPDATE oftTask SET T_CompleteFlag=? where username=? and T_DateTime=?";

	private static final String LOAD_TASKTYPE_LIST="select * from oftTaskType";
	
	private TaskManager() {
	}
	
	public static TaskManager getInstance(){
		
		return taskManager;
	}
	
	public List<TaskEx> queryTaskList(int start, int range) {
		Connection connection = null;
		List<TaskEx> result = null;
		try {
			QueryRunner runner = new QueryRunner();
			connection = DbConnectionManager.getConnection();
			result = runner.query(connection, LOAD_TASK_LIST,
					new BeanListHandler<TaskEx>(TaskEx.class));
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(connection);
		}
		return result;
	}
	
	public List<TaskType> queryTaskTyprList() {
		Connection connection = null;
		List<TaskType> result = null;
		try {
			QueryRunner runner = new QueryRunner();
			connection = DbConnectionManager.getConnection();
			result = runner.query(connection, LOAD_TASKTYPE_LIST,
					new BeanListHandler<TaskType>(TaskType.class));
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(connection);
		}
		return result;
	}
	
	public long queryTaskNum() {
		Connection connection = null;
		long result = 0 ;
		try {
			QueryRunner runner = new QueryRunner();
			connection = DbConnectionManager.getConnection();
			result = (Long) runner.query(connection, TASK_COUNT,new ScalarHandler());
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(connection);
		}
		return result;
	}
	
	private int setParam(PreparedStatement pstmt,String value, int cursor) throws SQLException{
		return setParam(pstmt, value, cursor, true);
	}
	
	private int setParam(PreparedStatement pstmt,int value, int cursor) throws SQLException{
		return setParam(pstmt, value, cursor, true);
	}
	
	private int setParam(PreparedStatement pstmt,String value, int cursor,boolean required) throws SQLException{
		if(value != null){
			cursor ++ ;
			pstmt.setString(cursor, value);
		}
		return cursor;
	}
	
	private int setParam(PreparedStatement pstmt,int value, int cursor,boolean required) throws SQLException{
		cursor ++ ;
		pstmt.setInt(cursor, value);
		return cursor;
	}
	
	public void addTask(String username , String T_Name,String T_ExecDateTime,
			String T_Para,String T_Cnt,String T_Interval,String T_MainVer,String T_TaskVer,String T_FuncName,String T_FuncVer ) throws ParseException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getTransactionConnection();
			pstmt = con.prepareStatement(ADD_TASK);
			int i = 0;
			i = setParam(pstmt, username, i);
			i = setParam(pstmt, StringUtils.dateToMillis(new Date()), i);
			
			i = setParam(pstmt, T_Name, i);
			SimpleDateFormat f = new SimpleDateFormat("yyyyMMddhhmm");
			Date date = f.parse(T_ExecDateTime);
			i = setParam(pstmt, StringUtils.dateToMillis(date), i);
			i = setParam(pstmt, T_Para, i);
			i = setParam(pstmt, Integer.parseInt(T_Cnt), i);
			i = setParam(pstmt, Integer.parseInt(T_Interval), i);
			i = setParam(pstmt, Integer.parseInt(T_MainVer), i);
			i = setParam(pstmt, Integer.parseInt(T_TaskVer), i);
			i = setParam(pstmt, T_FuncName, i);
			i = setParam(pstmt, Integer.parseInt(T_FuncVer), i);
			i = setParam(pstmt, 0, i);
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeTransactionConnection(con, true);
		}
	}
	
	public void forceCancel(String username,String datetime) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getTransactionConnection();
			pstmt = con.prepareStatement(UPDATE_TASK_FLAG);
			pstmt.setInt(1, Constants.T_CompleteFlag.forceCancel.value());
			pstmt.setString(2, username);
			pstmt.setString(3, datetime);	
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeTransactionConnection(con, true);
		}
	}
}